package gbench.sandbox.jdbc;

import org.junit.jupiter.api.Test;

import java.io.File;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.time.temporal.TemporalField;
import java.time.temporal.TemporalUnit;
import java.time.Duration;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.Random;
import java.util.UUID;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicLong;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.function.Supplier;
import java.util.regex.Pattern;
import java.util.stream.Collector;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import gbench.common.fs.FileSystem;
import gbench.common.fs.XlsFile;
import gbench.common.fs.XlsFile.DataMatrix;
import gbench.common.fs.XlsFile.StrMatrix;
import gbench.common.matlib.MatlibCanvas;
import gbench.common.matlib.MatlibCanvas.NVec;
import gbench.common.matlib.MatlibCanvas.XVec;
import gbench.common.matlib.data.DataReader.DFrame;
import gbench.common.matlib.matrix.Matrix;
import gbench.common.matlib.rdd.BasePair;
import gbench.common.matlib.rdd.PVec;
import gbench.common.tree.LittleTree;
import gbench.common.tree.LittleTree.ExceptionalConsumer;
import gbench.common.tree.LittleTree.ExceptionalFunction;
import gbench.common.tree.LittleTree.ExceptionalSupplier;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.Jdbc;
import gbench.common.tree.LittleTree.SQL;
import gbench.common.tree.LittleTree.Term;
import gbench.common.tree.LittleTree.Jdbc.DataManipulation;
import gbench.common.tree.LittleTree.Jdbc.IJdbcSession;
import gbench.common.tree.LittleTree.Jdbc.JdbcConfig;
import gbench.common.tree.LittleTree.Jdbc.JdbcExecute;
import gbench.common.tree.LittleTree.Jdbc.JdbcQuery;
import gbench.commonApp.data.DataMatrixApp;
import gbench.commonApp.jdbc.IJdbcApp;
import gbench.commonApp.jdbc.ISqlDatabase;

import static java.time.LocalDateTime.now;
import static gbench.common.matlib.MatlibCanvas.XVec.xveclc;
import static gbench.common.matlib.MatlibCanvas.println;
import static gbench.common.matlib.data.DataReader.DFrame.dfmclc;
import static gbench.common.matlib.rdd.BasePair.bp;
import static gbench.common.tree.LittleTree.IRecord.*;
import static gbench.common.tree.LittleTree.SQL.nsql;
import static gbench.common.tree.LittleTree.Term.FT;
import static gbench.common.tree.LittleTree.JOIN;
import static gbench.common.tree.LittleTree.MFT;
import static gbench.common.tree.LittleTree.kvp;
import static gbench.common.tree.LittleTree.trycatch;
import static gbench.common.tree.LittleTree.trycatch2;
import static gbench.common.tree.LittleTree.trycatch3;
import static gbench.common.tree.LittleTree.Jdbc.XJdbcSession.OPEN;

@SuppressWarnings("unused")
public class JunitERP {
    
    /**
     * mysql 数据库操作接口<br>
     * MysqlDatabase <br>
     * <br>
     * mysql> show variables like '%time_zone%' <br>
     * system_time_zone | China Standard Time <br>
     * <br>
     * DST: Daylight Saving Time中文名叫“夏令时”，一般在天亮早的夏季人为将时间调快一小时，<br>
     * 可以使人早起早睡，减少照明量，以充分利用光照资源，从而节约照明用电。中国1986-1991年实行夏令时，1992年废除。 <br>
     * GMT-8是东八区，北京时间和东八区一致。 <br>
     * CST: China Standard Time（老外认为有其他含义，中国就这个缩写），中国标准时。 中国1986-1991年实行夏令时，1992年废除。<br>
     * Asia/Shanghai是已地区命名的地区标准时，在中国叫CST。<br>
     * GMT：Greenwich Mean Time，格林威治标准时，地球每15°经度 被分为一个时区，共分为24个时区，相邻时区相差一小时；例: 中国北京位于东八区。 <br>
     * 1992年以后，在中国，GMT+8和Asia/Shanghai是一样的时间，1986-1991之间，夏天会有一小时时差。 <br>
     * 这就是 为何这里把时区写成Asia/Shanghai而不写成 GMT+8 (GMT%2B8) 的原因，因为 中国在 1986-1991 使用夏令时。<br>
     * 时间 Asia/Shanghai '1989-05-02 00:18:00' 在 GMT+8 中 为 '1989-05-02 01:18:00'
     * @author gbench
     *
     */
    @JdbcConfig(url = "jdbc:mysql://localhost:3306/erp?serverTimezone=Asia/Shanghai", user = "root", password = "123456")
    interface MysqlDatabase extends ISqlDatabase { // 数据接口
    }
    
    // 库房
    public static class StoreHouse{
        String code;// 编号
        String name;// 名称
        String address;// 名称
        String description;
    }
    
    /**
     * 用户
     * @author gbench
     *
     */
    public static class User{
        String id;
        String name;
    }
    
    /**
     * 用户
     * @author gbench
     *
     */
    public static class PlanFile{
        String id;
        String code;
        String name;
        String meetingId;
        String description;
    }
    
    /**
     * 
     * @author gbench
     *
     */
    public static class ChronoSpan {

        /**
         * 默認構造
         */
        public ChronoSpan() {
            // do nothing
        }

        /**
         * 时间跨度
         * 
         * @param startTime
         * @param endTime
         */
        public ChronoSpan(final LocalDateTime startTime, final LocalDateTime endTime) {
            if (endTime == null || startTime == null) {
                println("起止时间不能为null");
                return;
            }
            
            if (endTime.isAfter(startTime)) {
                this.startTime = startTime;
                this.endTime = endTime;
            } else { // 两点
                this.startTime = endTime;
                this.endTime = startTime;
            }
        }
        
        /**
         * 时段的持续时间
         * @param unit
         * @return
         */
        public Duration getDuration() {
            final var duration = Duration.between(this.startTime,this.endTime);
            return duration;
        }

        /**
         * 时段
         * @param startTime
         * @param endTime
         */
        public ChronoSpan(final String startTime, final String endTime) {
            this(LocalDateTime.parse(startTime, dtfmt), LocalDateTime.parse(endTime, dtfmt));
        }
        
        /**
         * 定义时段
         * @param startTime 开始时间
         * @param duration 时段的跨度
         */
        public ChronoSpan(final LocalDateTime startTime, final Duration duration) {
            this(startTime,startTime.plus(duration));
        }

        /**
         * 区间分段
         * 
         * @param bp 分段单位
         * @return 时段流
         */
        public Stream<ChronoSpan> slideS(final BasePair<? extends Number, ? extends TemporalUnit> bp) {
            return this.slideS(bp._1().longValue(), bp._2());
        }

        /**
         * 區間分段
         * 
         * @param duration 区段长度
         * @param unit     时长单位
         * @return 时段流
         */
        public Stream<ChronoSpan> slideS(final long duration, final TemporalUnit unit) {
            if (this.startTime == null || this.endTime == null) {
                return Stream.of();  // 返回一个空的区间流
            }else {
                return Stream.iterate( // 區間分段
                    ChronoSpan.of(origin, this.startTime), // seed
                    span -> !span.startTime.isAfter(this.endTime), // hasNext
                    span -> { // next
                        final var s = span.endTime;
                        final var e = s.plus(duration, unit == null ? ChronoUnit.DAYS : unit);
                        return ChronoSpan.of(s, e);
                    } // next
                ); // iterate
            }// if
        }

        /**
         * 
         */
        public String toString() {
            if(this.startTime==null && this.endTime==null) return null;
            return MessageFormat.format("({0},{1})", dtfmt.format(this.startTime), dtfmt.format(this.endTime));
        }

        /**
         * 
         * @param startTime
         * @param endTime
         * @return
         */
        public static ChronoSpan of(final LocalDateTime startTime, final LocalDateTime endTime) {
            return new ChronoSpan(startTime, endTime);
        }

        public LocalDateTime startTime;
        public LocalDateTime endTime;
        public final static String pattern = "yyyy-MM-dd HH:mm:ss";
        public final static DateTimeFormatter dtfmt = DateTimeFormatter.ofPattern(pattern);
        public final static LocalDateTime origin = LocalDateTime.parse("1970-01-01 00:00:00", dtfmt); // 时间的起点
    }
    
    /**
     * 准备产品数据
     */
    @Test
    public void product() {
        final var path = "D:/sliced/files/products/";
        final var home = new File(path);
        final var ll = new LinkedList<IRecord>();
        final var ai = new AtomicInteger(1);
        FileSystem.tranverse(home, e ->{ // 遍历图片库
            final var name = e.getName(); // 获取图片文件名称
            final var rec = IRecord.REC(
                "#id",ai.getAndIncrement(), // 产品id
                "name",name.substring(0,name.lastIndexOf(".")), // 产品名称
                "path",e.getAbsolutePath().replace("\\", "/"), // 产品图片路径
                "optional","-", // 附加信息
                "labels",name.substring(0,name.lastIndexOf(".")), // 产品标签
                "price",Math.random()*100, // 参考价格
                "money",1, // 计价货币
                "flag",0, // 有效标记
                "description",name, // 产品说明
                "update_time",LocalDateTime.now(), // 更新时间
                "create_time",LocalDateTime.now() // 创建时间
            );
            ll.add(rec);
        });
        final var mx = ll.stream().collect(DFrame.dfmclc);
        println(mx);
        build(mx,"t_product");
        jdbc.withTransaction(sess->{
            var tbl = "t_product";
            var tt = sess.sql2u("select * from "+tbl+"", DFrame.dfmclc);
            println(tt);
        });
        
    }
    
    /**
     * 实体明细(分项)关系表 结构的构建
     * 
     */
    @Test
    public void entity_item_rel() {
        final var path = "D:/sliced/files/products/valuables/gold.jpg";
        final var now = LocalDateTime.now(); // 当前系统时间
        final var defs = Arrays.asList( // 表定义
            bp("t_entity_item_rel", REC("#id",1,"path",path,"name","模版数据", "optional","可选信息","entity_id",1,"item_id",1,"drcr",0,"quantity",1d,
                "amount",1d,"flag",0,"ref",0,"description","测试说明","update_time",now,"create_time",now)) // 实体明细关系
            ,bp("t_contract", REC("#id",1,"path",path,"partA","甲方","partB","乙方","name","产品采购","amount",1d,
                "flag", 1 , "create_time",now)) // flag 表示合同类型
            ,bp("t_journal", REC("#id",1,"path",path,"name","交易事项名称","description","交易事项的会计分录","create_time",now))
            ,bp("t_changelog", REC("#id",1,"tbl","t_entity_item_rel","rowid",1,"col","quantity","old","0","new","1","flag",1,
                "description","-","create_time",now)) // 变更日志, flag 表示变更类型
            ,bp("t_datacache",REC("#id",1,"name","-","app","-","value",0d,"volume",0,"flag",0,"start_time",now,"end_time",now,
                "update_time",now,"create_time",now)) // 数据缓存,flag 数据是否有效
        ); // defs
        
        defs.stream() // 表定义的遍历
        .map(def -> def.fmap2(rec -> Stream.of(rec).collect(DFrame.dfmclc))) // 制作dframe
        .forEach(p -> { // (table_name,dframe)
            build(p._2(), p._1()); // 构件表
        }); // Stream
        
        final Function<BasePair<Integer,String>,String> render = p->"\t".repeat(p._1())+p._2(); // 数据缩进的渲染
        
        jdbc.withTransaction(sess -> { // 创建数据表
            final var tbl = "t_entity_item_rel";
            println(sess.sql2u(FT("select * from $0", tbl), DFrame.dfmclc));
            
            defs.stream() // 为表定义增加 触发器
            .filter(e -> !"t_changelog".equals(e._1())) // 剔除掉 t_changelog
            .map(e->{ // 创建触发器
                final var tblname = e._1(); // 表名
                final var flds = e._2().keys().stream().map(SQL::parseFieldName) // 提取 defs中定义的 表 的 字段名
                    .map(r->r.str("name")).filter(k->!"id".equalsIgnoreCase(k)); // rec 的 字段名列表 去除了'id' 项, 用于 设计 字 defs 的 表的字段变更触发器。
                final var cols = "rowid,flag,tbl,col,`old`,`new`,create_time"; // t_changelog 的列名字段，即变更记录的数据格式 用于 insert 插入数据
                final var lines = new LinkedList<BasePair<Integer,String>>(); // 语句行
                
                lines.add(bp(0, FT("CREATE TRIGGER `$0_AFTER_UPDATE` AFTER UPDATE ON `$0` FOR EACH ROW BEGIN",tblname))); // 触发器的名字
                flds.flatMap(col -> Stream.of(
                    bp(1,FT("if (old.`$0` is null and not isnull(new.`$0`)) or (not new.`$0` = old.`$0`) then",col)), // 条件开始, 由于 select if (null=null,0,1) 返回 1， 所以要对null 特别处理, 防止 null->null的条件触发
                    bp(2,FT("insert into t_changelog($0) values (old.id,0,'$1','$2',old.`$2`,new.`$2`,now());",cols,tblname,col)), // 插入数据记录,flag:0,表示系统更改
                    bp(1,FT("end if;")) // 条件结束
                )).forEach(lines::add); // 字段的修改记录
                lines.add(bp(0, FT("END;",tblname))); // 触发器的结束语句,tblname 没有使用,只是为了与 第一条语句在格式上进行呼应，哈哈
                
                return lines.stream().map(render).collect(Collectors.joining("\n"));
            }).peek(sql -> {
                System.out.println("-- 创建触发器 sql:\n" + sql);
                System.out.println(sql);
            }).forEach(trycatch3(sess::sql2execute));
        }); // withTransaction
    }

    /**
     * 业务处理流程
     * --------------------------
     * 召开会议：<br>
     *  -问题讨论：方案文件 <br>
     *  -制定决策：决策文件 <br>
     *  -- 执行计划 <br>
     *  -- 商业合同 <br>
     *  -- 组织任命 <br>
     */
    @Test
    public void datainit() {
        final var excel = new XlsFile.SimpleExcel(erpdata_file);
        final Function<DataMatrix<String>, DFrame> dfm = mx -> mx.removeRow(0).setHeader(mx.row(0)) .mapByRow(IRecord::REC).collect(DFrame.dfmclc);
        final var goods = excel.autoDetect("物资字典").mutate(dfm);
        final var items = excel.autoDetect("物资明细").mutate(dfm);
        final var user = excel.autoDetect("员工").mutate(dfm);
        final var house = excel.autoDetect("库房").mutate(dfm);
        final var org = excel.autoDetect("组织机构").mutate(dfm);
        final var meetings = excel.autoDetect("会议").mutate(dfm);
        final var trans = excel.autoDetect("出入库记录").mutate(dfm);
        final var prices = excel.autoDetect("价格表").mutate(dfm);
        final var plan = excel.autoDetect("执行文件").mutate(dfm);
        final var coa = excel.autoDetect("会计科目表").mutate(dfm);
        final var pharms = excel.autoDetect("常用药品").mutate(dfm);
        final var categories = excel.autoDetect("分类").mutate(dfm);
        final var t_fortune = excel.autoDetect("世界500强").mutate(dfm);
        final var t_voucher = excel.autoDetect("会计凭证").mutate(dfm);
        final var t_line_item = excel.autoDetect("发票项目").mutate(dfm);
        final var t_settlement = excel.autoDetect("结算方式").mutate(dfm);
        final var t_money = excel.autoDetect("货币").mutate(dfm);
        final var t_catalog = excel.autoDetect("产品目录").mutate(dfm);
        
        println(goods);
        println(prices);
        println(trans);
        println(user);
        println(org);
        println(meetings);
        println(trans);
        println(plan);
        println(coa);
        println(pharms);
        
        build(user,"t_user");
        build(goods,"t_sku");
        build(house,"t_house");
        build(items,"t_items");
        build(trans,"t_storage");
        build(meetings,"t_meeting");
        build(plan,"t_planfile");
        build(coa,"t_coa");
        build(pharms,"t_pharm");
        build(t_fortune,"t_fortune");
        build(categories,"t_category");
        build(t_voucher,"t_voucher");
        build(t_line_item,"t_line_item");
        build(t_settlement,"t_settlement");
        build(t_money,"t_money");
        build(t_catalog,"t_catalog");
        
        jdbc.withTransaction(sess->{ // 查看库房
            final var sql = "select g.drcr,u.name as uname,"
                + "g.quantity,h.name as hname,g.description,g.docId,p.name pname "
                + "from t_sku u inner join t_storage g inner join t_house h inner join t_planfile p "
                + "on u.id = g.skuId and h.id=g.houseId and g.docId=p.id";
            final var tt = sess.sql2u(sql, DFrame.dfmclc);
            println("---------------------------");
            println(tt);
        });
        
        jdbc.withTransaction(sess->{
            var tbl = "t_user";
            var tt = sess.sql2u("select * from "+tbl+" limit 1", DFrame.dfmclc);
            println(tt);
            
            tbl = "t_planfile";
            tt = sess.sql2u("select * from "+tbl+" ", DFrame.dfmclc);
            println(tt);
        });
        
        // 设置实体关系表
        entity_item_rel();
        excel.close();
    }
    
    @Test
    public void jd() {
        final var excel = new XlsFile.SimpleExcel(erpdata_file);
        final Function<DataMatrix<String>, DFrame> dfm = mx -> mx.removeRow(0).setHeader(mx.row(0)) .mapByRow(IRecord::REC).collect(DFrame.dfmclc);
        final var t_miaosha = excel.autoDetect("秒杀").mutate(dfm);
        final var t_country = excel.autoDetect("country").mutate(dfm);
        this.build(t_miaosha, "t_miaosha"); // 秒杀
        this.build(t_country, "t_country"); // 国家
        excel.close();
    }
    
    /**
     * 创建数据表
     * @param def 数据定义 (DFrame,String)
     */
    public void build(final BasePair<String,DFrame> def) {
        this.build(def._2(),def._1());
    }

    /**
     * 创建数据表
     * @param df 数据定义
     * @param tableName
     */
    public void build(final DFrame df,final String tableName) {
        final var dfm = df.rowsS(rec -> {
            rec.computeIfPresent(0, v -> { // 主键id的调整
                final var num = obj2dbl().apply((v + "").strip());
                if (num == null) {
                    return v;
                } else {
                    return num.intValue();
                } // if
            }); // dfm 调整后的数据框
            return rec;
        }).collect(DFrame.dfmclc); // 调整数据框的主键主键类型
        
        final var sqls = SQL
            .of(tableName, dfm.row(0).aoks2rec(e->e.equalsIgnoreCase("id")?"#id":e))
            .createTable(true); // 创建表
        println("#创建表:",tableName);
        jdbc.withTransaction(sess -> {
            XVec.OF(sqls).delete(1).stream().peek(System.out::println).forEach(trycatch2(sess::sql2execute)); // 创建一个单表结构
            dfm.rowsS().map(SQL.bd(tableName)::get).map(SQL::insert)
            //.peek(System.out::println)
            .forEach(trycatch2(sess::sql2execute));
        }); // withTransaction
    }
    
    /**
     * entity 统计
     * @param reltbl
     */
    public void stats(final String reltbl) {
        jdbc.withTransaction(sess -> {
            final var sql = MFT("select name,count(*) cnt from {0} group by name", reltbl);
            final var dfm = sess.sql2u(sql, dfmclc);
            dfm.rowsS(e -> e.compute("name", (String v) -> v.split("/"))).filter(e -> e.length >= 2).map(ss -> {
                final var entitytbl = ss[0]; // 实体表
                final var itemtbl = ss[1]; // 项目表
                final var reltbl2 = MFT("(select * from {0} where name like \"%{1}\")", reltbl, itemtbl); // 新的关系表
                final var flds = "r.drcr,r.entity_id,e.id,e.name,r.optional,e.price,r.quantity,r.amount"; // 字段列表
                final var items_sql = MFT("select {0} from {1} e inner join {2} r on r.item_id=e.id", flds, itemtbl, reltbl2);
                final var transactions = XVec.OF(items_sql)
                    .fmap(trycatch((String s) -> sess.sql2stream(s).collect(xveclc()))).car()
                    .collect(PVec.pvec1clc(e -> e.str("entity_id"))) //  item 按照entity_id 进行分组
                    .groupByKey().unwind().mutate(e -> { // (entityid,items)
                        final var entityids = e._1();
                        final var items = e._2();
                        final var entities_sql = MFT("select * from {0} where id in ({1})", entitytbl, e._1().collect(Collectors.joining(",")));
                        final var entities = XVec.OF(entities_sql).fmap(trycatch((String s) -> sess.sql2u(s, XVec.xveclc()))).car();
                        final var bodies = e._2().fmap(p -> p.collect(dfmclc));
                        return entities.zip(bodies, BasePair::bp);
                    }); // mutate
                return BasePair.bp(entitytbl, transactions); // 交易摘要
            }).forEach(section -> {
                println(section._1(), "-----------------------------");
                section._2().forEach(bp -> {
                    final var entity = bp._1();
                    final var entries = bp._2();
                    println("\n", entity);
                    println(entries);
                }); // forEach bp
            }); // forEach section
        });// withTransaction
    }
    
    @Test
    public void finance() {
        stats("t_entity_item_rel");
    }
    
    /**
     * 模拟商业系统
     */
    @Test
    public void simulateBusiness() {
        
        this.entity_item_rel();// 准备实体关系
        final var startTime = LocalDateTime.parse("1980-10-01 00:00:00",ChronoSpan.dtfmt); // 开始时间
        final var entity_proto = REC("path","D:/sliced/files/products/valuables/gold.jpg",
            "partA",0,"partB",0,"name","1","amount","0","create_time",startTime);
        final var entity_item_proto = REC("path", "D:/sliced/files/products/valuables/gold.jpg", // 图片路径
            "name", "t_contract/t_product", "optional", "产品明细", "entity_id", 1, "item_id", 1, // 生成产品合同
            "drcr", 0, "quantity", 1, "amount", 1, "flag",0 ,"description", "-", "create_time", startTime);
        final var flds = entity_item_proto.keys(); // item 的字段
        final var rnd = new Random(); // 随机数
        
        jdbc.withTransaction(OPEN(DFrame.dfmclc, sess -> {
            final ExceptionalFunction<IRecord, XVec<XVec<IRecord>>> item_set_list_read = (param) -> { // 生成产品列表
                final var itemset_list = sess.sql2u("select * from t_product") // 提取产品列表
                    .shuffle().collect(xveclc()).resize(500) // 扩充产品容量至500项目
                    .sliceX(NVec.of(10, i -> rnd.nextInt(5) + 1).ints()); // 随机生成产品组合
                return itemset_list;
            };
            final var abX = sess.sql2u("select * from t_fortune limit 100") // 提取 公司列表
                .shuffle().collect(xveclc()) // 重新排序公司
                .sliceX(BasePair::bptt, 2); // 交易双方
            final var serializer = new AtomicLong(System.nanoTime()); // 序列号
            final var counter = new AtomicInteger(0);// 计数器
            
            abX.forEach(ab -> { // 甲乙双方
                final var bp = ab.map((a, b) -> BasePair.bp(a.i4("id"), b.i4("id"))); // 提取交易双方的 id
                final var item_set_list = trycatch(item_set_list_read).apply(null); // 生成明细列表
                item_set_list.fmap(itemset -> { // 产品项目清单
                    final var createTime = startTime.plus(counter.getAndAdd(rnd.nextInt(24*60)),ChronoUnit.MINUTES); // 模拟创建时间
                    final var protoLines = itemset.fmap(item -> {
                        final var quantity = rnd.nextInt(10) + 1; // 生成数量
                        final var amount = quantity * item.dbl("price"); // 计算 行项目金额 
                        final var protoLine = entity_item_proto.derive( // 实体关系集合,明细清单
                            "item_id", item.str("id"), "quantity", rnd.nextInt(10) + 1, "price", item.dbl("price"), "amount", amount, // 交易主体
                            "description", item.str("name"), "create_time", createTime);
                        return protoLine; // 生成行项目原型
                    }) ;// itemset.fmap 生成明细行项目的原型
                    final var amount = protoLines.reduce(0d, (a, r) -> a + r.dbl("price") * r.dbl("quantity"), (a, b) -> a + b); // 计算行项目 金额
                    final var name = MFT("{0}/{1}/{2,number,#}", ab._1().str("name"), ab._2().str("name"), serializer.getAndIncrement()); // 合同名称
                    final var entity = entity_proto.derive("partA", bp._1(), "partB", bp._2(), "name", name, "amount", amount, "create_time", createTime);
                    final var entity_sql = SQL.of("t_contract", entity).insert(); // 实体的创立语句
                    final var entity_id = trycatch((String sql) -> sess.sql2execute2int(sql)).apply(entity_sql); // 实体主键
                    final var entity_item_set = protoLines.fmap(protoLine -> protoLine.add("entity_id", entity_id).remove("price")); // 添加 实体id并 剔除 价格
                    
                    return entity_item_set; // 供应 实体明细关系 结集合
                }).fmap(entity_item_set -> { // 明细清单
                    final var tpl = FT("insert into t_entity_item_rel (flds$) values ${foreach item in items (values$)}",
                         REC("flds$", JOIN(flds, ","), "values$", JOIN(flds.stream(),i -> "item." + i, ","))); // 明细项目的插入模板
                    return nsql(tpl, REC("items", entity_item_set.toArray()));
                }).map(trycatch(sess::sql2execute2num)) // 插入明细项目
                .forEach(id -> { // 输出 明细 id
                    println("明细",id);
                }); // forEach
            }); // abX.forEach
        })); // withTransaction
    }
    
    /**
     * 数据核算应用<br>
     * 
     * dataTable 需要具有 create_time,update_time 以方便构建 核算缓存. <br>
     * 需要注意 dataTable 不一定需要是一个表名，也可以使 一条 视图语句 即 select 查询。
     * 
     * @author gbench
     *
     */
    class MyDataApp {
        
        /**
         * 核算应用: <br>
         * dataTable 需要具有 create_time,update_time 以方便构建 核算缓存 <br>
         * 需要注意 dataTable 不一定需要是一个表名，也可以使 一条 视图语句 即 select 查询。
         * 
         * @param name [table]/[label] t_contract/amountApp
         */
        public MyDataApp(final String name) {
            this(name, name.split("/")[0].strip(), MyDataApp.DEFAULT_CACHE_TABLE);
        }
        
        /**
         * 核算应用<br>
         * 需要注意 dataTable 不一定需要是一个表名，也可以使 一条 视图语句 即 select 查询。
         * 
         * @param name      应用名称
         * @param dataTable 业务数据表, dataTable 需要具有 create_time,update_time 以方便构建 核算缓存
         */
        public MyDataApp(final String name, final String dataTable) {
            this(name, dataTable, null);
        }
        
        /**
         * 核算应用: <br>
         * 需要注意 dataTable 不一定需要是一个表名，也可以使 一条 视图语句 即 select 查询。<br>
         * 
         * @param name       应用名称
         * @param dataTable  业务数据表 或是 可以生成对应业务数据视图 的 sql语句, dataTable 需要具有
         *                   create_time,update_time 以方便构建 核算缓存
         * @param cacheTable 中间结果的缓存表
         */
        public MyDataApp(final String name, final String dataTable, final String cacheTable) {
            this.appName = name;
            this.dataTable = isTableName(dataTable) 
                    ? dataTable // 简单的 数据表名
                    : (FT("($0) as _datatbl", dataTable)); // 数据表,比如 t_contract/amountApp
            this.cacheTable = cacheTable == null ? MyDataApp.DEFAULT_CACHE_TABLE : cacheTable;
        }
        
        /**
         * 检查时间区段 span 是否有效 <br>
         * data的数据是否晚于cache项目,true cache 项目需要更新, false cache 项目不需要更新。
         * 
         * @param <S>  session 对象
         * @param span [startTime,endTime)
         * @param sess 缓存的应用名称
         * @return data的数据是否晚于cache项目,true cache 项目需要更新, false cache 项目不需要更新。
         * @throws Exception
         * @throws Throwable
         */
        public <S extends IJdbcSession<UUID, Object>> Boolean isExpired(final ChronoSpan span, final S sess) throws Exception,Throwable {
            
            final var cache_sql = nsql( "select if(update_time is null,create_time,update_time) update_time from ##cachetbl "
                + " where #app = app and #startTime = start_time and #endTime = end_time ", 
                IRecord.OBJ2REC(span, "startTime,endTime").add(REC("app", this.appName,"cachetbl",this.cacheTable))); // 缓存项的更新时间
            final var data_sql = FT("select max(if(update_time is null,create_time,update_time)) update_time from $0 "
                + " where create_time >= '$1' and create_time < '$2'",this.dataTable,span.startTime,span.endTime); // 数据区段的更新时间
            final var data_update_time_opt = sess.sql2maybe(data_sql).map(e -> e.ldt("update_time"));
            final var cache_update_time_opt = sess.sql2maybe(cache_sql).map(e -> e.ldt("update_time"));
            final var opt = data_update_time_opt.map(dut -> cache_update_time_opt.map(cut -> dut.isAfter(cut))) // dut:数据跟新时间 cut:缓存项更新时间
                .orElse(Optional.of(false)); // data的数据是否晚于cache项目; 数据项目空缺,表示不需要更新 
            final var flag = opt.orElse(false); // 数据是否失效
            
            // println("data_update_time", data_update_time_opt, "cache_update_time", cache_update_time_opt, flag ? "需要更新" : "不需要更新");
            
            return flag;
        }
        
        /**
         * span统计 <br>
         * 时段的数据计算, 带有数据缓存功能的统计计算
         * 
         * @param <S>          session 对象
         * @param span         [startTime,endTime)
         * @param sess         session 对象
         * @param expiredCheck 是否做过期检查
         * @return 統計結果
         */
        public <S extends IJdbcSession<UUID, Object>> IRecord handle(final ChronoSpan span, final S sess ,final boolean expiredCheck) throws Exception,Throwable {
            
            final ExceptionalSupplier<Optional<IRecord>> cache_item_read = () -> { // 缓存项目读取
                final var sql = nsql( // id,name,app,value,volume,flag,start_time,end_time,update_time,create_time
                    "select * from ##cachetbl where #app = app and #startTime = start_time and #endTime = end_time", 
                    IRecord.OBJ2REC(span, "startTime,endTime").add(REC("app", this.appName,"cachetbl",this.cacheTable))); // 缓存项的检查
                return sess.sql2maybe(sql); // 读取缓存项目sql
            }; // cache_item_getter
            final ExceptionalSupplier<IRecord> cache_item_calculate = () -> { // 数据核算
                final var sql = FT( // name,volume,value
                    "select concat('$1','-','$2') name, count(*) volume, ifnull(sum(amount),0) value from $0 where create_time >= '$1' and create_time < '$2'", 
                    this.dataTable, span.startTime, span.endTime); // 计算任务
                return sess.sql2get(sql);
            }; // cache_item_calculator
            final ExceptionalFunction<IRecord,Integer> cache_item_insert = (item)->{ // 插入的缓存项目
                return sess.sql2execute2int(SQL.of(this.cacheTable, item).insert());
            };
            final ExceptionalFunction<IRecord,Integer> cache_item_update = (item)->{ // 数据更新内容
                return sess.sql2execute2int(SQL.of(this.cacheTable, item).update2()); 
            };
            
            final var optional = cache_item_read.get(); // 提取缓存项目
            final var rec = REC("app", this.appName, "flag", 0, "start_time", span.startTime, 
                "end_time", span.endTime, "create_time", now()); // 默认返回结果
            
            if (optional.isEmpty() || optional.get().i4("flag") != 0 || (expiredCheck && this.isExpired(span, sess)) ) { // 没有缓存项 或是 缓存项无效
                final var result = rec.add(cache_item_calculate.get()); // 数据核算
                if (optional.isEmpty()) { // 缓存项目 不存在
                    if (Math.abs(result.dbl("value")) > 0) { // cacheItem 值有效
                        final var ret = cache_item_insert.apply(result); // 数据统计
                        println(span, "插入 cache_item", ret);
                    } else { // result 区间计算的结果为0
                        println("data none or 0!","无数据 或 核算结果为 0", span);
                        return null;
                    } // if cacheItem
                } else { // 缓存项目 无效
                    final var id = optional.get().get("id"); // 提取缓存项目id
                    final var item = REC("id", id, "flag", 0, "update_time", now()) // 清空flag,并记录 更新时间
                            .add(result.filter("volume,value")); // 更新 重新计算后的 volume 和 value
                    final var ret = cache_item_update.apply(item); // 保存更新项目
                    println("cache 重计算！", id, rec); // 执行数据更新
                } // optional
            } else { // 存在数据项目
                rec.add(optional.get()); // 直接使用缓存结果
                println("cache 命中！", optional.get().get("id"), optional);
            } // if optional
            
            return rec;
        }
        
        /**
         * 计算数据的时间在fld之上的跨度
         * 
         * @param tbl 数据表名或是 数据视图(语句)
         * @param fld 数据维度的字段名称
         * @return Optional<IRecord> rec[start,end]
         */
        public Optional<IRecord> dataRange(final String tbl, final String fld) {
            final var sql_range = FT("select min($1) start, max($1) end from $0",
                isTableName(tbl)
                    ? tbl  // 数据表明
                    : (FT("($0) tbl", tbl)), // 数据视图 (语句)
                fld );
            // 计算时间跨度
            return jdbc.sql2maybe(sql_range);
        }
        
        /**
         * 获取数据在 fld维度上的 数据范围
         * 
         * @param tbl       数据表名或者数据表视图(语句)
         * @param chronoFld 时间维度字段
         * @return ChronoSpan 的 Optional
         */
        public Optional<ChronoSpan> getPeriod(final String tbl,final String chronoFld){
            
            return this.getPeriod(tbl,chronoFld,null);
        }
        
        /**
         * 获取数据的时间跨度
         * 
         * @param tbl       数据表
         * @param chronoFld 时间字段
         * @param mapper    变换函数
         * @return 数据的时间跨度
         */
        public Optional<ChronoSpan> getPeriod(final String tbl,final String chronoFld,Function<IRecord,ChronoSpan> mapper){
            final Function<IRecord, ChronoSpan> _mapper = mapper != null 
                ? mapper // 指定的变换函数
                : e ->e.keys("startTime,endTime").mutate(ChronoSpan.class); // 默认的变换函数 
            return this.dataRange(tbl, chronoFld).map(_mapper);
        }
        
        /**
         * 获取数据的时间跨度
         */
        public Optional<ChronoSpan> getPeriod() {
            final Function<IRecord,ChronoSpan> mapper = e->e.keys("startTime,endTime").mutate(ChronoSpan.class);
            return this.getPeriod(this.dataTable,"create_time",mapper);
        }
        
        /**
         * 对 数据缓存 进行 刷新
         * @param <S> 会话类型
         * @param sess 会话对象
         * @param chronoSpan 期间跨度
         * @param duration 期间跨度
         * @param unit 跨度单位
         */
        public <S extends IJdbcSession<UUID, Object>> void validate(final S sess, final ChronoSpan chronoSpan, final long duration, final TemporalUnit unit) {
            final var final_chrono_span = chronoSpan == null ? this.getPeriod().get() : chronoSpan; // 计算chronoSpan
            final_chrono_span.slideS(duration, unit).forEach(trycatch3(span -> { // 时间的分段 检测
                if(this.isExpired(span, sess)) {
                    final var sql = FT("update $0 set flag = $1 where '$2' = app and '$3' = start_time and '$4' = end_time",
                        this.cacheTable, 1, this.appName, span.startTime, span.endTime);
                    sess.sqlexecute(sql);// 标记业务缓存项为无效
                } // if
            })); // forEach
        }

        /**
         * 数据核算
         * 
         * @param acct_period    核算的时间跨度
         * @param duration      分区区间
         * @param unit          时长单位
         * @param validate_flag 是否做缓存状态更新
         */
        public IRecord evaluate(final ChronoSpan acct_period, final long duration, final TemporalUnit unit, final boolean validate_flag) {
            final var result = new AtomicReference<IRecord>(); // 返回结果
            final var final_chrono_span = acct_period == null ? this.getPeriod().get() : acct_period; // 计算chronoSpan
            println("实际核算的 会计期间为:",final_chrono_span);
            jdbc.withTransaction(OPEN(DFrame.dfmclc, sess -> {
                
                println("-----------------------------------------------------------------");
                if (validate_flag) { // 调整缓存项目的状态,由于这是位于事务之内，所以对缓存数据的修改会把appName的行给锁住,即对这些行的更改不能离开当前事务。
                    println("数据有效性检测");
                    this.validate(sess, final_chrono_span, duration, unit); // 这也是为何 validate 需要参数 sess的原因
                    println("-----------------------------------------------------------------");
                } // validate_flag
                
                final var dfm = final_chrono_span.slideS(duration, unit) // 时间的分段
                     // 由于clear 锁住了app的行所以这里的handle需要把sess作为参数传递以保持不离开事务，否则造成死锁
                     // 不能 使用 jdbc.withTransaction 做 span的handle,因为事务锁表了appName的行了。
                    .map(trycatch(span -> this.handle(span, sess, false))) 
                    .filter(Objects::nonNull) // 去除空对象
                    .collect(DFrame.dfmclc); // 归集分片数据
                
                println("-----------------------------------------------------------------");
                println(dfm);
                println("-----------------------------------------------------------------");
                
                result.set(REC("total", dfm.rowsS().reduce(0d, (a, r) -> a + r.dbl("value"), (a, b) -> a + b))); // 返回结果
                println("total 缓存计算", result.get().get("total"));
                final var sql = FT("select sum(amount) total from $0",this.dataTable);
                println("total 整体计算", sess.sql2get(sql).get("total"));
                println("total sql:",sql);
            })); // withTransaction
            
            return result.get();
        }
        
        /**
         * 数据核算 不对缓存做 validate
         * @param duration 分区区间
         * @param unit 时长单位
         */
        public IRecord evaluate(final long duration, final TemporalUnit unit) {
            return this.evaluate(null,duration, unit, false);
        }
        
        /**
         * 获取跨度span内的缓存项目
         * @param span 数据跨度
         * @return 获取跨度span内的缓存项目
         */
        public Optional<IRecord> getCacheItem(final ChronoSpan span) {
            final var ai = new AtomicInteger(0);
            final var r = IRecord.OBJ2REC(span,"startTime,endTime");
            final var sql = SQL.nsql("select * from ##cachetbl where #app=app and #s = start_time and #e = end_time", 
                IRecord.OBJ2REC(span, "startTime,endTime").add(REC("app", this.appName, "cachetbl", this.cacheTable))).string2();
            // println(sql);
            return jdbc.sql2maybe(sql);
        }
        
        /**
         * 获取时刻ldt所在缓存项目
         * @param ldt 时刻点
         * @return 获取时刻ldt所在缓存项目
         */
        public IRecord findCacheItem(final LocalDateTime ldt) {
            final var ai = new AtomicInteger(0);
            final var sql = SQL.nsql("select * from ##cachetbl where app=#app and #time >= start_time and #time < end_time",
                REC("app", this.appName, "cachetbl", this.cacheTable, "time", ldt)).string2();
            println(sql);
            return jdbc.sql2maybe(sql).orElse(null);
        }
        
        /**
         * 判断字符串是否一个表名
         * 
         * @param line 字符串
         * @return 判断字符串是否一个表名
         */
        public boolean isTableName(String line) {
            return line.matches("[_a-zA-Z0-9]+");
        }
        
        final String appName; // 应用名称
        final String dataTable;  // 业务数据表
        final String cacheTable; // 中间结果的缓存表
        
        final static String DEFAULT_CACHE_TABLE = "t_datacache";
    }
    
    /**
     * 账套的初始化
     */
    @Test
    public void finance_data_initialize() {
        final var now = LocalDateTime.now();
        Stream.of( 
            bp("t_transaction", REC("id", 1, "name", "交易名称", "create_time", now)), // 交易-核算 对象
            bp("t_double_entry", REC("id", 1, "~acct_id", -1, "~line_id", -1,  // acct_id+line_id 做为唯一约束
                    "transcode", -1, "entity_id", -1, "item_id", -1, "amount", 0d, "description", "-", "update_time", now, "create_time", now))
        ).map(p -> p.fmap2(r -> Stream.of(r).collect(DFrame.dfmclc)))
        .forEach(this::build);
        
        final var flds = "transcode,acct_id,entity_id,item_id, abs(amount) amount ,create_time, update_time";
        final var v_double_entry = FT("create or replace view v_double_entry as select if(amount>=0,0,1) drcr, $0 from t_double_entry", flds);
       
        println("\n 创建一个分类账数据视图", "v_double_entry", v_double_entry, "\n");
        jdbc.withTransaction(sess -> {
            sess.sql2execute(v_double_entry);
        });// withTransaction
    }
    
    /**
     * 誊写分类账 <br>
     * 
     * 需要 注意 这里 entity_id 需要根据上下文 来 理解, t_entity_item_rel 中的 entity_id 指向的是 t_contract(id) <br>
     * 而 提供给 ledger_entry_tbl 中的 entity_id 实则是 acct_entity_id 是对应于 t_contract(partA or partB) 需要注意区分
     * 
     * @param batch_mode     批处理模式 true 批处理, false 单条的数据处理
     * @param acct_entity_id 会计实体编号
     * @param line_id        行项目编号
     * @param rel_name        关系名称,比如:t_contract/t_product
     * @param acct_method    entry->[line_id:明细行码,transcode:交易代码,entity_id:主体代码,item_id:明细编码,amount:金额,create_time:交易时间]
     */
    public void post_to_ledger(final Integer acct_entity_id, final Integer line_id, final String rel_name, final Function<IRecord,Stream<IRecord>> acct_method) {
        final var line_tbl = FT( "select "
            + " $0 entity_id, b.id line_id, a.id transcode, a.name, if($0=a.partA,1,-1)*if(b.drcr=0,1,-1) drcr, b.item_id, b.quantity, b.amount, a.create_time "
            + " from ( select * from t_contract where partA=$0 or partB=$0) a inner join (select * from t_entity_item_rel where id = $1) b "
            + " on a.id = b.entity_id "
            , acct_entity_id,line_id); // 行项目
        final var ledger_entry_tbl = FT("select line_id, transcode, name, entity_id, item_id, drcr*amount as amount, create_time from ($0) tbl", line_tbl); // 科目分录
        final var sql = ledger_entry_tbl; // acct_entity_id 的 账册数据sql
        
        final var now = LocalDateTime.now(); // 当前系统时间
        final var lstclc = Collectors.toList(); // 连接函数
        final var joinclc = Collectors.joining(","); // 连接函数
        final Function<Object, String> formatter = v -> "'" + v + "'"; // 值格式化函数
        final var acct_entry_flds = "acct_id,line_id,transcode,entity_id,item_id,amount,create_time"; // 分录结构
        
        jdbc.withTransaction(sess -> {
            sess.sql2stream(sql).flatMap(acct_method).map(e -> e.filter(acct_entry_flds)).map(e -> { // 
                final var flds = e.keys().stream().collect(joinclc); // 更新字段
                final var insval = e.valueS(formatter).collect(joinclc); // 插入值
                final var uptval = e.add("update_time", now).stream(kvp -> kvp.map((k, v) -> FT("$0='$1'", k, v))).collect(joinclc); // 更新值
                return FT("insert into t_double_entry($0) values ($1) on duplicate key update $2", flds, insval, uptval); // 数据的插入语句
            })
            .peek(System.out::println)
            .forEach(trycatch2(sess::sqlexecute)); // sql2stream
        });
    }
    
    /**
     * 誊写分类账 <br>
     * 
     * 需要 注意 这里 entity_id 需要根据上下文 来 理解, t_entity_item_rel 中的 entity_id 指向的是 t_contract(id) <br>
     * 而 提供给 ledger_entry_tbl 中的 entity_id 实则是 acct_entity_id 是对应于 t_contract(partA or partB) 需要注意区分
     * 
     * @param batch_mode  批处理模式 true 批处理, false 单条的数据处理
     * @param acct_period      会计期间
     * @param acct_entity_id   会计实体
     * @param rel_name        关系名称,比如:t_contract/t_product
     * @param acct_method entry->[line_id:明细行码,transcode:交易代码,entity_id:主体代码,item_id:明细编码,amount:金额,create_time:交易时间]
     */
    public void post_to_ledger(final boolean batch_mode, final ChronoSpan acct_period, final Integer acct_entity_id, String rel_name,
            final Function<IRecord,Stream<IRecord>> acct_method) {
        
        final var entity_tbl = FT("select id, if(partA=$2,1,-1) drcr, name, create_time "
            + " from t_contract where (partA=$2 or partB=$2) and create_time >= '$0' and create_time <= '$1'",
            acct_period.startTime, acct_period.endTime, acct_entity_id); // 提取业务合同
         //println("entity_tbl",entity_tbl); // 交易方表
        
        final var item_tbl = FT("select b.id line_id, a.id transcode, a.name, a.drcr*if(b.drcr=0,1,-1) drcr, b.item_id, b.quantity, b.amount, a.create_time "
            + " from ($0) a inner join (select * from t_entity_item_rel where name = '$1') b on a.id = b.entity_id order by create_time", entity_tbl, rel_name); // 提取交易明细 a:交易,b:产品明细
        //println("item_tbl",item_tbl);
        
        final var ledger_entry_tbl = FT("select line_id, transcode, name, $1 entity_id, item_id, drcr*amount as amount, create_time from ($0) tbl",
            item_tbl, acct_entity_id); // 科目分录
        //println("ledger_entry_tbl",ledger_entry_tbl);
        
        final var sql = ledger_entry_tbl; // acct_entity_id 的 账册数据sql
        //println("sql",sql);
        
        final var now = LocalDateTime.now(); // 当前系统时间
        final var lstclc = Collectors.toList(); // 连接函数
        final var joinclc = Collectors.joining(","); // 连接函数
        final Function<Object, String> formatter = v -> "'" + v + "'"; // 值格式化函数
        final var acct_entry_flds = "acct_id,line_id,transcode,entity_id,item_id,amount,create_time"; // 分录结构
        
        jdbc.withTransaction(OPEN(DFrame.dfmclc, sess -> { // 数据会话
            final var h0 = acct_entry_flds; // 分录结构的字段形式
            final var h1 = XVec.OF(h0.split(",")).map(e -> "e." + e).collect(Collectors.joining(",")); // 分录结构的 (对象.字段) 形式
            
            if (batch_mode) { // 批量插入模式
                final var pattern = FT("insert into t_double_entry($0) values ${foreach e in entries ($1)}", h0, h1);
                sess.sqlexecute(nsql(pattern, REC("entries", // 复式记账的分录
                        sess.sql2stream(sql).flatMap(acct_method).collect(lstclc) // 复式记账
                ))); // sqlexecute
            } else { // 单条插入
                sess.sql2stream(sql).flatMap(acct_method).map(e -> e.filter(acct_entry_flds)).map(e -> { // 
                    final var flds = e.keys().stream().collect(joinclc); // 更新字段
                    final var insval = e.valueS(formatter).collect(joinclc); // 插入值
                    final var uptval = e.add("update_time", now).stream(kvp -> kvp.map((k, v) -> FT("$0='$1'", k, v))).collect(joinclc); // 更新值
                    return FT("insert into t_double_entry($0) values ($1) on duplicate key update $2", flds, insval, uptval); // 数据的插入语句
                })
                //.peek(System.out::println)
                .forEach(trycatch2(sess::sqlexecute)); // sql2stream
            } // if batch_mode
            
            println("-----------------------------------------------------------------");
            println(sess.sql2u("select * from v_double_entry limit 10"));
        })); // withTransaction
    }
    
    /**
     * 会计核算
     */
    @Test
    public void accounting() {
        
        final var reset = true ; // 重置财务核算数据
        if (reset) { // 重置财务核算数据
            this.finance_data_initialize(); // 财务表表格初始化
        }
        
        final var acct_entity_id = 97; // 实例主体id
        final var dr_acct_id = 1;// 借方账户
        final var cr_acct_id = 2;// 贷方账户
        final var ledger_data = FT("select * from v_double_entry where acct_id = $0",dr_acct_id); // 分类账数据
        final var name = FT("t_entry/contract/$0-$1",acct_entity_id,dr_acct_id); // 核算项目名
        final var dataApp = new MyDataApp(name, ledger_data); // 销售合同的金额核算应用
        final var acct_entity_data = FT( // 核算主体数据集
            "select * from t_contract where partA=$0 or partB=$0",acct_entity_id); // 提取核算实体的交易合同数据
        final var acct_period = dataApp.getPeriod(acct_entity_data, "create_time").get();
        println("核算的会计期间为",acct_period);
        
        final Function<IRecord, Stream<IRecord>> acct_method = e -> { // 复式记账
            println("[posting 交易项目]", e);
            final var standard = BasePair.bp(dr_acct_id, cr_acct_id); // 标准方法
            final var d = e.dbl("amount"); // 带符号的金额
            final var acct_ids = d >= 0 ? standard : standard.swap(); // 根据金额符号 调整 借贷方向
            final var amount = Math.abs(d); // 金额绝对值
            final var dr_entry = e.derive(REC("acct_id", acct_ids._1(),"amount", amount)); // 借方账户
            final var cr_entry = e.derive(REC("acct_id", acct_ids._2(), "amount", -amount)); // 贷方账户
            
            return Stream.of(dr_entry,cr_entry); // 生成复式借账分录
        }; // acct_method 记账法
        
        println("-----------------------------------------------------------------");
        println("誊写分类账 of ",acct_entity_id);
        final var rel_name = "t_contract/t_product";
        if (reset) { // 数据重做
            this.post_to_ledger(false, acct_period, acct_entity_id,rel_name, acct_method); // 把entity_id中的数据誊写到核算账簿
        } else { // 明细项目更新
            final var sql = FT("select a.name, b.* from (select * from t_contract where partA=$0 or partB=$0) a "
                    + " inner join t_entity_item_rel b on b.entity_id = a.id limit 1", acct_entity_id); // 提取行项目
            println("明细项目的检索sql:",sql);
            jdbc.sql2maybe(sql).ifPresent(e -> {
                println("更新的明细项目为:",e);
                final var line_id = e.i4("id"); // 提取行项目 id
                this.post_to_ledger(acct_entity_id, line_id,rel_name, acct_method);
            }); // sql2maybe
        } // if reset
        
        println("-----------------------------------------------------------------");
        final var result = dataApp.evaluate(acct_period, 10, ChronoUnit.DAYS, true); // 带有缓存刷新的数据核算
        println(result);
    }
    
    /**
     * 缓存计算
     */
    @Test
    public void foo() {
        final var dataApp = new MyDataApp("t_contract/amount/total"); // 销售合同的金额核算应用
        this.simulateBusiness(); // 准备数据
        dataApp.evaluate(6, ChronoUnit.MONTHS); // 按照半年的跨度进行数据缓存
        
        println("-----------------------------------------------------------------");
        final var ldt = LocalDateTime.parse("2018-03-07 00:08:00", ChronoSpan.dtfmt);
        println(ldt, "的数据定位", dataApp.findCacheItem(ldt));
        println("-----------------------------------------------------------------");
        println(ldt, "is before", ldt, ldt.isBefore(ldt));
        
        final var cspan = ChronoSpan.of(ldt, ldt.plus(1,ChronoUnit.MINUTES)); // 区段范围
        final var bp =BasePair.bp(25,ChronoUnit.SECONDS); // 分段的时间单位
        println(cspan, "按照的区段分解如下 ", bp , "\n");
        cspan.slideS(bp).map(kvp()).forEach(p -> {
            println(p._1(),p._2());
        }); // slideS
    }
    
    final String filename = true?"erp_data.xlsx":"F:/slicef/desktop/tmp/medicine/erp_data.xlsx"; // erp的数据文件
    final File erpdata_file = new File(FileSystem.path(filename, this.getClass()));
    final MysqlDatabase database = IJdbcApp.newNspebDBInstance("db3.sql",MysqlDatabase.class); // 创建数据库像
    final IRecord proxy = database.getProxy(); // 提取代理对象
    final Jdbc jdbc = proxy.findOne(Jdbc.class); // 获取jdbc对象

}